package com.roc.dao.impl;
/**
 * @author FengHaiTao
 * 
 */
import java.io.Serializable;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.time.DateFormatUtils;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.NonUniqueResultException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.stereotype.Repository;

import com.roc.dao.DaoException;
import com.roc.dao.setting.ISettingFlagDao;
import com.roc.page.WhereHQLFieldBean;
import com.roc.pojo.AuditingItem;
import com.roc.pojo.CalcuBean;
import com.roc.pojo.DuizhangBean;
import com.roc.pojo.Operators;
import com.roc.pojo.SettFlagBean;
import com.roc.pojo.SettingBean;
import com.roc.pojo.SettingItemBean;
import com.roc.service.ServiceException;
import com.roc.util.Constance;
import com.roc.util.Utils;
import com.roc.web.action.setting.CalcuSearchCondition;
import com.roc.web.action.setting.ReviewSettingSearchCondition;

@Repository
public class SettingFlagImpl extends BaseDaoImpl implements ISettingFlagDao {

	@Override
	public List<Object> querycalcuSummoney(CalcuSearchCondition searchCondition) throws DaoException {
		 List<Object> objs=new ArrayList<Object>();
		try {
			Session session =sessionFactory.getCurrentSession(); 
			StringBuffer sql=new StringBuffer(" select nvl(sum(t.AMOUNT),0) totalamt,nvl(sum(t.merfee),0) totalfee,nvl(sum(t.calcuamount),0) totalcalcuamt,nvl(COUNT(*),0) totalnum from t_transjnls_calcu t where t.sett_flag='Y' ");
			
			
			if (Constance.isNotEmpty(searchCondition.getMerNo())) {
				sql.append(" and t.merno = '"+searchCondition.getMerNo()+"'");
			}
			if (Constance.isNotEmpty(searchCondition.getAccount())) {
				sql.append(" and t.account = '"+searchCondition.getAccount()+"'");
			}
			if (Constance.isNotEmpty(searchCondition.getEtstrace())) {
				sql.append(" and t.ETSTRACE = '"+searchCondition.getEtstrace()+"'");
				
			}
			if (Constance.isNotEmpty(searchCondition.getEtstermId())) {
				sql.append(" and t.etstermId = '"+searchCondition.getEtstermId()+"'");
			}

			if (Constance.isNotEmpty(searchCondition.getMinDate()) && Constance.isNotEmpty(searchCondition.getMaxDate())) {
				//Date startDate = Constance.formateDate(minDate);
				//Date endDate = Constance.formateDate(maxDate);
				String startDate = searchCondition.getMinDate().replace("-", "");
				String endDate = searchCondition.getMaxDate().replace("-", "");
				sql.append(" and t.ETSDATE >= '"+searchCondition.getMinDate()+"'");
				sql.append(" and t.ETSDATE <= '"+searchCondition.getMaxDate()+"'");
	
			}
	      
	        logger.info(sql.toString());
				SQLQuery query = session.createSQLQuery(sql.toString());
				 objs= query.list();
				
				
		} catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询清算数据失败！");
		}
		
		return objs;
	}
	@Override
	public List<SettFlagBean> querySettingFlagList() throws DaoException {
		List<SettFlagBean> orgsettingflagList=new ArrayList<SettFlagBean>();
		try {
			Session session = sessionFactory.getCurrentSession();
			String hql="from T_SETTINGFLAG_INFO  ";
			Query query=session.createQuery(hql);
			orgsettingflagList=query.list();
		} catch (HibernateException e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询结算标示出错");
		}
		return orgsettingflagList;
	}
	@Override
	public boolean frozenApply(AuditingItem auditing,
			SettingBean sett) throws DaoException {
		boolean result=false;
		try {
			/*******添加审核结果成功---记录日志*******/
			editSave(auditing);
			/*******修改清算冻结成功--------记录日志*******/
			editUpdate(sett);
			
			result=true;
		} catch (DaoException e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("审核");
		}
		
		return result;
	}
	@Override
	public SettingBean querySettingBean(String id) throws DaoException {
		SettingBean result = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query = session.createQuery("from SettingBean m where m.id = :id");
			query.setString("id", id);
			result = (SettingBean) query.uniqueResult();
		}catch(NonUniqueResultException nure){
			logger.debug("查询SettingBean不唯一", nure);
			throw new DaoException("查询结算记录不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询出错");
		}
		return result;
	}
	@Override
	public SettingBean querySettingBean(String merchantno,String bathno) throws DaoException {
		SettingBean result = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query = session.createQuery("from SettingBean m where m.merchantno = :merchantno and m.orderno=:orderno");
			query.setString("merchantno", merchantno);
			query.setString("orderno", bathno);
			result = (SettingBean) query.uniqueResult();
		}catch(NonUniqueResultException nure){
			logger.debug("查询SettingBean不唯一", nure);
			throw new DaoException("查询结算记录不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询出错");
		}
		return result;
	}
	@Override
	public int queryStatusCount(List<Long> iddlist) throws DaoException {
		int i =0;
		try {
			Session session =sessionFactory.getCurrentSession(); 
			StringBuffer sql=new StringBuffer(" select count(*) from t_transjnls_settle t where  t.settleflag in ('Y','H') AND t.freezeflag='N' and  t.id in (");
				for(int j=0;j<iddlist.size();j++){
					if(j!=iddlist.size()-1){
						sql.append(iddlist.get(j)+",");
					}else{
						sql.append(iddlist.get(j));
					}
				}
				sql.append(")");
				SQLQuery query = session.createSQLQuery(sql.toString());
				String str= (String)query.uniqueResult().toString();
				if(str !=null){
					i = Integer.parseInt(str);
				}
				
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询失败！");
		}
		
		return i;
	}
	
	@Override
	public int querycaucleStatusCount(List<String> iddlist,String bathno) throws DaoException {
		int i =0;
		try {
			Session session =sessionFactory.getCurrentSession(); 
			StringBuffer sql=new StringBuffer("    select count(*) from t_transjnls_calcu t where   t.settbatch='"+bathno+"' and t.etstrace in ('");
				for(int j=0;j<iddlist.size();j++){
					if(j!=iddlist.size()-1){
						sql.append(iddlist.get(j)+"','");
					}else{
						sql.append(iddlist.get(j));
					}
				}
				sql.append("')");
				SQLQuery query = session.createSQLQuery(sql.toString());
				String str= (String)query.uniqueResult().toString();
				if(str !=null){
					i = Integer.parseInt(str);
				}
				
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询失败！");
		}
		
		return i;
	}
	@Override
	public double querySummoney(ReviewSettingSearchCondition searchCondition) throws DaoException {
		double i =0;
		try {
			Session session =sessionFactory.getCurrentSession(); 
			StringBuffer sql=new StringBuffer(" select nvl(sum(t.totalamt),0) total from t_transjnls_settle t where t.freezeflag='N' and t.needsett='0' ");
			
			if (Constance.isNotEmpty(searchCondition.getMerNo())) {
				sql.append(" and t.merchantno = '"+searchCondition.getMerNo()+"'");
			}
			if (Constance.isNotEmpty(searchCondition.getRealName())) {
				sql.append(" and t.merchantname like '%"+searchCondition.getRealName()+"%'");
			}
	        if(Constance.isNotEmpty(searchCondition.getMinDate())&&Constance.isNotEmpty(searchCondition.getMaxDate())){
	        	//Date startDate=Constance.formateDate(minDate);
	        	//Date endDate =Constance.formateDate(maxDate);
	        	String startDate = searchCondition.getMinDate().replace("-", "");
				String endDate = searchCondition.getMaxDate().replace("-", "");
				sql.append(" and t.settledate >= '"+startDate+"' ");
				sql.append(" and t.settledate <= '"+endDate+"' ");
	        }
	        if(Constance.isNotEmpty(searchCondition.getAmount())){
	       	 if("1".equals(searchCondition.getAmount())){
	       		sql.append(" and t.merchantsettleamt < 1 ");
	       	 }
	       	 if("2".equals(searchCondition.getAmount())){
	       		sql.append(" and t.merchantsettleamt >= 1 ");
	       	 }
	        }
	        logger.info(sql.toString());
				SQLQuery query = session.createSQLQuery(sql.toString());
				String str= (String)query.uniqueResult().toString();
				if(str !=null){
					i = Double.valueOf(str);
				}
				
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询失败！");
		}
		
		return i;
	}
	
	
	@Override
	public void excuCalcuPro() throws DaoException {
		SettingBean result = null;
		try {
			Session session =sessionFactory.getCurrentSession(); 
			//获取hibernate会话
			String procName="{ call initTranjsjnlsCalcu() }"; 
			
			SQLQuery query = session.createSQLQuery(procName);
			query.executeUpdate();
		}catch(NonUniqueResultException nure){
			logger.debug("查询SettingBean不唯一", nure);
			throw new DaoException("查询结算记录不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询出错");
		}
		
	}
	
	@Override
	public void excuSettingPro() throws DaoException {
		int result = 0;
		try {
			Session session =sessionFactory.getCurrentSession(); 
			//获取hibernate会话
			String procName="{ call initTranjssetting(?,?) }"; 
			
			SQLQuery query = session.createSQLQuery(procName);
			String time="";
			Date dt = new Date();  
		    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");  
		    time=sdf.format(dt); 
			query.setString(0,time);
			String settingtime = Utils.getConfigValue("settingtime");
			query.setString(1,settingtime);
			 query.executeUpdate();
		}catch(NonUniqueResultException nure){
			logger.debug("查询SettingBean不唯一", nure);
			throw new DaoException("查询结算记录不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询出错");
		}
	}
	@Override
	public void excuSettingLoadPro() throws DaoException {
		
		try {
			Session session =sessionFactory.getCurrentSession(); 
			//获取hibernate会话
			String procName="{ call INITTRANJSSETTINGLOAD(?,?) }"; 
			
			SQLQuery query = session.createSQLQuery(procName);
			String time="";
			Date dt = new Date();  
		    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");  
		    time=sdf.format(dt); 
			query.setString(0,time);
			String settingtime = Utils.getConfigValue("settingtime");
			query.setString(1,settingtime);
			query.executeUpdate();
		}catch(NonUniqueResultException nure){
			logger.debug("查询SettingBean不唯一", nure);
			throw new DaoException("查询结算记录不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询出错");
		}
		
	}
	@Override
	public int excuKQSettingPro(String minDate,String maxDate) throws DaoException {
		int result = 0;
		try {
			Session session =sessionFactory.getCurrentSession(); 
			//获取hibernate会话
			String procName="{ call KQSETTINGOPER(?) }"; 
			
			SQLQuery query = session.createSQLQuery(procName);
			
			query.setString(0,minDate);
			//query.setString(1,maxDate);
			String settingtime = Utils.getConfigValue("settingtime");
			//query.setString(1,settingtime);
			result = query.executeUpdate();
		}catch(NonUniqueResultException nure){
			logger.debug("查询SettingBean不唯一", nure);
			throw new DaoException("查询结算记录不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询出错");
		}
		return result;
	}
	@Override
	public int excuHRTSettingPro(String minDate,String maxDate) throws DaoException {
		int result = 0;
		try {
			Session session =sessionFactory.getCurrentSession(); 
			//获取hibernate会话
			String procName="{ call HRTSETTINGOPER(?) }"; 
			
			SQLQuery query = session.createSQLQuery(procName);
			query.setString(0,minDate);
			//query.setString(1,maxDate);
			String settingtime = Utils.getConfigValue("settingtime");
			//query.setString(1,settingtime);
			result = query.executeUpdate();
		}catch(NonUniqueResultException nure){
			logger.debug("查询SettingBean不唯一", nure);
			throw new DaoException("查询结算记录不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询出错");
		}
		return result;
	}
	@Override
	public void excuCleanDatePro() throws DaoException {
		SettingBean result = null;
		try {
			Session session =sessionFactory.getCurrentSession(); 
			//获取hibernate会话
			String procName="{ call initTranjsjnls() }"; 
			
			SQLQuery query = session.createSQLQuery(procName);
			query.executeUpdate();
		}catch(NonUniqueResultException nure){
			logger.debug("查询SettingBean不唯一", nure);
			throw new DaoException("查询结算记录不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询出错");
		}
		
	}
	@Override
	public String maxserid(String settdate) throws DaoException {
		List list = new ArrayList();
		String ss ="1";
		try {
			Session session =sessionFactory.getCurrentSession(); 
			//获取hibernate会话
			//String sql=" select nvl(max(t.serial),1)  as serial from t_settle_item t "; 
			String sql=" select nvl(max(t.serial),1)  as serial from t_settle_item t where t.settledate='"+settdate+"' ";
			SQLQuery query = session.createSQLQuery(sql);
			 list =   query.list();
		   if(list.size()>0){
			  ss = list.get(0).toString();
		   }
				
			
		
		}catch (Exception e) {
			logger.debug(e.getMessage(), e);
			System.out.println(e.getMessage());
			throw new DaoException("审核");
		}
		return ss;
	}
	
	@Override
	public boolean reviewsetting(AuditingItem auditing,
			SettingBean sett,List<SettingItemBean> list) throws DaoException {
		boolean result=false;
		try {
			/*******添加审核结果成功---记录日志*******/
			editSave(auditing);
			/*******修改清算冻结成功--------记录日志*******/
			editUpdate(sett);
			for(int i=0;i<list.size();i++){
				SettingItemBean settitem = new SettingItemBean();
				settitem = list.get(i);
				editSave(settitem);
			}
			result=true;
		} catch (DaoException e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("审核");
		}
		
		return result;
	}
	@Override
	public void updatesetting(String id) throws DaoException {
		
		try {
			Session session =sessionFactory.getCurrentSession(); 
			String time="";
			Date dt = new Date();  
		    SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");  
		    time=sdf.format(dt); 
			String sql=" update t_transjnls_settle t set  t.settleflag='D',t.setdate='"+time+"' WHERE t.id=(select settid from t_settle_item where id="+id+")";
			SQLQuery query = session.createSQLQuery(sql);
			query.executeUpdate();
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("更新失败！");
		}
		
		
	}	
	@Override
	public void updatesetting(String id,String settdate,String stuts) throws DaoException {
		
		try {
			Session session =sessionFactory.getCurrentSession(); 
		
		   
				String sql=" update t_transjnls_settle t set  t.settleflag='S' WHERE t.id=(select settid from t_settle_item where serial ='"+id+"'  and settledate='"+settdate+"')";
				SQLQuery query = session.createSQLQuery(sql);
				query.executeUpdate();
		    
		    
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("更新失败！");
		}
		
		
	}
	
	@Override
	public void updatesetitem(String id,String settdate,String stuts) throws DaoException {
		
		try {
			Session session =sessionFactory.getCurrentSession(); 
			String st="";
			String sql="";
			if(stuts.equals("5")){
				st="S";
			}
			if(stuts.equals("6")){
				st="F";
			}
		    if(!"".equals(st)){
				 sql=" update t_settle_item t set  t.status='"+st+"' WHERE t.serial ='"+id+"'  and t.settledate='"+settdate+"' ";
				
		    }else{
		    	sql="update t_settle_item t set  t.times=t.times+1 WHERE t.serial ='"+id+"'  and t.settledate='"+settdate+"'";
		    }
		    SQLQuery query = session.createSQLQuery(sql);
			query.executeUpdate();
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("更新失败！");
		}
		
		
	}
	
	@Override
	public int setitemcount(String id,String settdate) throws DaoException {
		int i =0;
		try {
			Session session =sessionFactory.getCurrentSession(); 
				String sql=" select COUNT(*) from t_settle_item t   WHERE t.status='S' AND t.settid=(select settid from t_settle_item where serial ='"+id+"'  and settledate='"+settdate+"')";
				SQLQuery query = session.createSQLQuery(sql);
				
				String str= (String)query.uniqueResult().toString();
				if(str !=null){
					i = Integer.parseInt(str);
				}
				
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("更新失败！");
		}
		
		return i;
	}
	
	
	@Override
	public int setitemtotalcount(String id,String settdate) throws DaoException {
		int i =0;
		try {
			Session session =sessionFactory.getCurrentSession(); 
				String sql=" select COUNT(*) from t_settle_item t   WHERE  t.settid=(select settid from t_settle_item where serial ='"+id+"'  and settledate='"+settdate+"')";
				SQLQuery query = session.createSQLQuery(sql);
				String str= (String)query.uniqueResult().toString();
				if(str !=null){
					i = Integer.parseInt(str);
				}
				
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("更新失败！");
		}
		
		return i;
	}
	@Override
	public CalcuBean queryCalcuBean(String date,String time,String termtrace) throws DaoException {
		CalcuBean result = null;
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query = session.createQuery("from CalcuBean m where m.etsdate = :date and m.etstime=:time and m.termtrace=:termtrace ");
			query.setString("date", date);
			query.setString("time", time);
			query.setString("termtrace", termtrace);
			result = (CalcuBean) query.uniqueResult();
		}catch(NonUniqueResultException nure){
			logger.debug("查询SettingBean不唯一", nure);
			throw new DaoException("查询结算记录不唯一");
		}catch (Exception e) {
			logger.error(e.getMessage(), e);
			throw new DaoException("查询出错");
		}
		return result;
	}
	@Override
	public boolean insertCHZ(Map<String,DuizhangBean> map,Map<String,DuizhangBean> map1) throws DaoException {
		boolean result=false;
		try {
			/*******对账文件插入查错账表*******/
			Iterator it = map.keySet().iterator();
			while(it.hasNext()){	
				Object key = it.next();  
				DuizhangBean dz = new DuizhangBean();
				 dz = (DuizhangBean) map.get(key); 
					//查询是否是因为本地撤销或者冲正引起的查错账。
					String reversal=selectreversal(dz.getLocallogno(),dz.getLocaldate(),dz.getLocaltime());
					if(reversal!=null&&!"".equals(reversal)){
						if("5".equals(reversal)){
							//通道有本地被撤销。
							dz.setDiffflag("3");
						}else if("1".equals(reversal)){
							//通道有本地冲正失败。
							dz.setDiffflag("4");
						}else if("2".equals(reversal)){
							//通道有本地被冲正。
							dz.setDiffflag("5");
						}
					}
				editSave(dz);
				
			}
			/*******本地数据文件插入查错账表*******/
			Iterator ite = map1.keySet().iterator();
			while(ite.hasNext()){	
				Object key = ite.next();     
				DuizhangBean dz = new DuizhangBean();
				dz = (DuizhangBean) map1.get(key); 
				editSave(dz);
			}
			result=true;
		} catch (DaoException e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("插入查错账表失败");
		}
		
		return result;
	}
	@Override
	public String selectreversal(String etstrace,String etsdate,String etstime) throws DaoException {
		String str ="";
		try {
			Session session =sessionFactory.getCurrentSession(); 
				String sql=" select t.reversal from ESTRNHISLOG t   WHERE  substr(t.etstrace,7,12)='"+etstrace+"'  and t.etsdate='"+etsdate+"' and t.etstime='"+etstime+"'";
				SQLQuery query = session.createSQLQuery(sql);
				if(query.uniqueResult()!=null&&!"".equals(query.uniqueResult())){
					str= (String)query.uniqueResult().toString();
				}
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询失败！");
		}
		
		return str;
	}
	
	
	
	@Override
	public int updatecalcu(List<String> iddlist,String bathno,String ordno) throws DaoException {
		int i =0;
		try {
			Session session =sessionFactory.getCurrentSession(); 
		
		   
			StringBuffer sql=new StringBuffer("  update t_transjnls_calcu t set  t.settbatch='"+ordno+"' WHERE  t.settbatch='"+bathno+"' and t.etstrace in ('");
				for(int j=0;j<iddlist.size();j++){
					if(j!=iddlist.size()-1){
						sql.append(iddlist.get(j)+"','");
					}else{
						sql.append(iddlist.get(j));
					}
				}
				sql.append("')");
				SQLQuery query = session.createSQLQuery(sql.toString());
				i=query.executeUpdate();
		    
		    
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("更新失败！");
		}
		
		return i;
	}
	@Override
	public int updatecalcu(DuizhangBean dz) throws DaoException {
		int i =0;
		try {
			Session session =sessionFactory.getCurrentSession(); 
			double d = Double.valueOf(dz.getTraamt());
			d = d/100;
			Date cDate = new Date();
			String cStr = DateFormatUtils.format(cDate, "yyyyMMdd");
			StringBuffer sql=new StringBuffer("  update t_transjnls_calcu t set  t.HOSTBRANTHNO='"+dz.getHostbranthno()+"',t.HOSTMERNO='"+dz.getHostmerno()+"',t.HOSTTERMNO='"+dz.getHosttermno()+"',t.dzdate='"+cStr+"' WHERE  substr(t.etstrace,7,12)='"+dz.getReqlogno()+"' and t.etsdate='"+dz.getReqdate()+"' and t.amount="+d+"");
			SQLQuery query = session.createSQLQuery(sql.toString());
			i=query.executeUpdate();
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("更新失败！");
		}
		
		return i;
	}
	
	
	
	@Override
	public List<Object> querySUM(String bathno,List<String> iddlist) throws DaoException {
		List<Object> settingflagList = new ArrayList<Object>();
		try {
			// settingflagList=settingFlagDao.querySettingFlagList();
			StringBuffer sql=new StringBuffer("    select sum(t.amount),sum(t.merfee),sum(t.calcuamount) from t_transjnls_calcu t where   t.settbatch='"+bathno+"' and t.etstrace in ('");
			for(int j=0;j<iddlist.size();j++){
				if(j!=iddlist.size()-1){
					sql.append(iddlist.get(j)+"','");
				}else{
					sql.append(iddlist.get(j));
				}
			}
			sql.append("')");
			
			List<Object> objList = new ArrayList<Object>();
			objList = queryAll(sql.toString());
			
		} catch (DaoException e) {
			logger.debug(e.getMessage());
		}
		return settingflagList;
	}
	//根据对账文件查询 本地交易数据
	@Override
	public int queryTrancount(DuizhangBean db)throws DaoException {
		int i =0;
		try {
			Session session =sessionFactory.getCurrentSession(); 
			StringBuffer sql=new StringBuffer(" select count(*) from ESTRNHISLOG s where 1=1 and s.etstrancode = '020000' and s.respcode = '00' and s.reversal='0'");
			if (StringUtils.isNotBlank(db.getReqlogno())) {
				sql.append(" and substr(s.etstrace,7,12)= '"+db.getReqlogno()+"'");
			}
			if (StringUtils.isNotBlank(db.getTraamt())) {
				double d = Double.valueOf(db.getTraamt());
				sql.append(" and s.amount = "+d);
			}
			SQLQuery query = session.createSQLQuery(sql.toString());
			String str= (String)query.uniqueResult().toString();
			if(str !=null){
				i = Integer.parseInt(str);
			}
				
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询失败！");
		}
		
		return i;
	}
	//根据对账文件查询 本地交易数据
	@Override
	public int queryDZcount(DuizhangBean db)throws DaoException {
		int i =0;
		try {
			Session session =sessionFactory.getCurrentSession(); 
			StringBuffer sql=new StringBuffer(" SELECT COUNT(*) FROM T_RECONCILIATION T where 1=1 ");
			if (StringUtils.isNotBlank(db.getLocallogno())) {
				sql.append(" and T.locallogno= '"+db.getLocallogno()+"'");
			}
			
//			if (StringUtils.isNotBlank(db.getHosttermid())) {
//				sql.append(" and s.hosttermid= '"+db.getHosttermid()+"'");
//			}
//			if (StringUtils.isNotBlank(db.getAccount())) {
//				sql.append(" and s.account= '"+db.getAccount()+"'");
//			}
			if (StringUtils.isNotBlank(db.getTraamt())) {
				double d = Double.valueOf(db.getTraamt());
				
				sql.append(" and T.TRAAMT = "+d+"");
			}
			
				SQLQuery query = session.createSQLQuery(sql.toString());
				String str= (String)query.uniqueResult().toString();
				if(str !=null){
					i = Integer.parseInt(str);
				}
				
		} catch (Exception e) {
			logger.debug(e.getMessage(), e);
			throw new DaoException("查询失败！");
		}
		
		return i;
	}
	@Override
	public void deleteDZ(DuizhangBean db) throws DaoException {
		
		Session session =sessionFactory.getCurrentSession(); 
		try {
		
		StringBuffer sql=new StringBuffer(" delete from T_RECONCILIATION t where  1=1 ");
		if (StringUtils.isNotBlank(db.getLocallogno())) {
			sql.append(" and T.locallogno= '"+db.getLocallogno()+"' ");
		}
		if (StringUtils.isNotBlank(db.getReqlogno())) {
			sql.append(" and T.reqlogno='"+db.getReqlogno()+"'");
		}
		if (StringUtils.isNotBlank(db.getTraamt())) {
			double d = Double.valueOf(db.getTraamt());
			sql.append(" and T.TRAAMT= "+d+"");
		}
		SQLQuery query = session.createSQLQuery(sql.toString());
		query.executeUpdate();
		
		} catch (Exception e) {
				logger.debug(e.getMessage());
		}
	}
	@Override
	public String selectExcelName(String modelflag,String excelflag) throws DaoException {
		List list = new ArrayList();
		String ss ="";
		try {
			Session session =sessionFactory.getCurrentSession(); 
			//获取hibernate会话
			//String sql=" select nvl(max(t.serial),1)  as serial from t_settle_item t "; 
			String sql=" select t.excelname from t_excelinfo t where t.modelflag ='"+modelflag+"' and t.excelflag='"+excelflag+"' and t.createdate=to_char(sysdate,'yyyymmdd') order by t.id desc ";
			SQLQuery query = session.createSQLQuery(sql);
			 list =   query.list();
		   if(list.size()>0){
			  ss = list.get(0).toString();
		   }
		}catch (Exception e) {
			logger.debug(e.getMessage(), e);
			System.out.println(e.getMessage());
			throw new DaoException("查询名称");
		}
		return ss;
	}
	@Override
	public String getsettid() throws DaoException {
		List list = new ArrayList();
		String ss ="";
		try {
			Session session =sessionFactory.getCurrentSession(); 
			String sql=" select seq_setting.nextval from dual ";
			SQLQuery query = session.createSQLQuery(sql);
			if(query.uniqueResult()!=null&&!"".equals(query.uniqueResult())){
				ss= (String)query.uniqueResult().toString();
			}

		}catch (Exception e) {
			logger.debug(e.getMessage(), e);
			System.out.println(e.getMessage());
			throw new DaoException("审核");
		}
		return ss;
	}
}
